﻿/*
=============================================================================
CreatedTime:2011-10-26
Description:三层结构数据访问层的通用数据库访问方法
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DAL
{
	///<summary>
	/// 数据库访问通用方法类
	/// </summary>
	public static class DBHelper
	{   
		//连接字符串
		private static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
		/// <summary>
        ///  不带参数的非查询方法（只有sql语句，不包含sql语句中参数值）  
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <returns>rowCount（影响的行数）</returns>
		public static int ExecuteCommand(string safeSql)
		{　 int rowCount;
		    using(SqlConnection connection = new SqlConnection(connectionString))
			{
				SqlCommand command = new SqlCommand(safeSql, connection);
				try
				{
					connection.Open();
                    rowCount = command.ExecuteNonQuery();
				}
			    catch (SqlException exception)
                {
                    connection.Close();
                    throw exception;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
			}
			
			return rowCount;
		}
		/// <summary>
        /// 带参数的非查询方法（sql语句，sql语句中参数值或数组SqlParameter[]）
        /// </summary>
        /// <param name="safeSql">safeSql </param>
        /// <param name="cmdParms"> SqlParameter[] cmdParms</param>
        /// <returns>rowCount(影响的行数)</returns>
		public static int ExecuteCommand(string safeSql, params SqlParameter[] cmdParms)
		{
			int rowCount;
		    using(SqlConnection connection = new SqlConnection(connectionString))
			{
				SqlCommand command = new SqlCommand();
				try
				{
					PrepareCommand(command, connection, null, safeSql, cmdParms);
                    rowCount = command.ExecuteNonQuery();
                    command.Parameters.Clear();
				}
			    catch (SqlException exception)
                {
                    connection.Close();
                    throw exception;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
			}
			return rowCount;
		}
		/// <summary>
        ///根据sql语句执行查询并返回结果集中的一样行第一列的值
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <returns>Object</returns>
		public static object GetScalar(string safeSql)
		{
			object obj;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(safeSql, connection);
                try
                {
                    connection.Open();
                    object objA = command.ExecuteScalar();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                    {
                        return null;
                    }
                    obj = objA;
                }
                catch (SqlException exception)
                {
                    connection.Close();
                    throw exception;
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return obj;
		}
		/// <summary>
        /// 根据sql语句和参数执行查询并返回结果集中的一样行第一列的值
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <param name="cmdParms">cmdParms</param>
        /// <returns>Object</returns>
		public static object GetScalar(string safeSql, params SqlParameter[] cmdParms)
		{
			object obj;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    PrepareCommand(cmd, connection, null, safeSql, cmdParms);
                    object objA = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                    {
                        return null;
                    }
                    obj = objA;
                }
                catch (SqlException exception)
                {
                    throw exception;
                }
                finally
                {
                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }
                }
            }
            return obj;
		}
		/// <summary>
        /// 根据sql语句检查某值的存在
        /// </summary>
        /// <param name="strSql"> strSql</param>
        /// <returns>bool</returns>
        public static bool Exists(string strSql)
        {
            int num;
            object single = GetScalar(strSql);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }
		 /// <summary>
        /// 根据sql语句和参数检查某值的存在
        /// </summary>
        /// <param name="strSql">strSql</param>
        /// <param name="cmdParms">params</param>
        /// <returns> bool</returns>
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int num;
            object single = GetScalar(strSql, cmdParms);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }
		
		/// <summary>
        /// 根据字段名和表名获得最大值（sql语句模型也创建）
        /// </summary>
        /// <param name="FieldName">字段名FielName</param>
        /// <param name="TableName">表名TableName</param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            object single = GetScalar("select max(" + FieldName + ")+1 from " + TableName);
            if (single == null)
            {
                return 1;
            }
            return int.Parse(single.ToString());
        }
		/// <summary>
        /// 不带参数的查询方法，只有算sql语句
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <returns>SqlDataReader reader</returns>
		public static SqlDataReader GetReader(string safeSql)
		{
			SqlDataReader reader;
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(safeSql, connection);
            try
            {
                connection.Open();
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException exception)
            {
                throw exception;
            }
            return reader;
		}
		/// <summary>
        ///带参数的查询方法，包含sql语句和对应的参数
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <param name="cmdParms">单个参数或SqlParameter[]数组</param>
        /// <returns>SqlDataReader GetReader</returns>
		public static SqlDataReader GetReader(string safeSql, params SqlParameter[] cmdParms)
		{
			SqlDataReader reader = null;
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(safeSql, conn);
                if (cmdParms != null)
                {
                    foreach (SqlParameter para in cmdParms)
                    {
                        if (para.Value == null)
                        {
                            para.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(para);
                    }
                }
                conn.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (SqlException sex)
            {
                throw new Exception(sex.ToString());
            }
            return reader;
		}
		
		/// <summary>
        /// 执行存储过程的方法
        /// </summary>
        /// <param name="cmd">SqlCommand cmd</param>
        /// <param name="conn"> SqlConnection conn</param>
        /// <param name="trans"> SqlTransaction trans</param>
        /// <param name="cmdText"> string cmdText</param>
        /// <param name="cmdParms"> SqlParameter[] cmdParms</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    //如果参数是空，或日期型值为系统初始值，则将参数设为DBNull.Value;
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)))
                    {
                        if (parameter.Value == null)
                            parameter.Value = DBNull.Value;

                        if (parameter.Value is DateTime && ((DateTime)parameter.Value) == DateTime.MinValue)
                            parameter.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(parameter);
                }
            }
        }
		
		/// <summary>
        /// 根据sql语句执行查询并获得表table数据
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <returns>DataTable table</returns>
		public static DataTable GetDataTable(string safeSql)
		{ 
			using (SqlConnection connection = new SqlConnection(connectionString))
            {
				DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(safeSql, connection);
					SqlDataAdapter da = new SqlDataAdapter(cmd);
					da.Fill(ds);
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                } 
				return ds.Tables[0];
            }
		}
		/// <summary>
        /// 根据sql语句和参数执行查询并获得表table数据
        /// </summary>
        /// <param name="safeSql">string safeSql</param>
        /// <param name="cmdParms">params SqlParameter[]</param>
        /// <returns>DataTable table</returns>
		public static DataTable GetDataTable(string safeSql, params SqlParameter[] cmdParms)
		{
			DataSet dset;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, safeSql, cmdParms);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    dset = dataSet;
                }
			}
			return dset.Tables[0];
		}
		
	    /// <summary>
        /// 根据sql语句执行查询并获得DateSet数据集
        /// </summary>
        /// <param name="safeSql">safeSql</param>
        /// <returns>DateSet dateSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    new SqlDataAdapter(SQLString, connection).Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                return dataSet;
            }
        }
		/// <summary>
        /// 根据sql语句和参数执行查询并获得DateSet数据集
        /// </summary>
        /// <param name="safeSql">string safeSql</param>
        /// <param name="cmdParms">params SqlParameter[]</param>
        /// <returns>DateSet dateSet</returns>
        public static DataSet Query(string safeSql, params SqlParameter[] cmdParms)
        {
            DataSet set2;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, safeSql, cmdParms);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    set2 = dataSet;
                }
            }
            return set2;
        }
        /// <summary>
        /// 根据sql语句和等待时间参数执行查询获得数据结果集
        /// </summary>
        /// <param name="safeSql">string safeSql</param>
        /// <param name="Times">int Times</param>
        /// <returns>dataSet</returns>
        public static DataSet Query(string safeSql, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(safeSql, connection);
                    adapter.SelectCommand.CommandTimeout = Times;
                    adapter.Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
					throw new Exception(exception.Message);
                }
                return dataSet;
            }
        }

	}
}
